All the Perl that's Practical to Extract and Report

Some Days I Feel Like I Know Nothing About Oracle

Mr. Muskrat on 2007-08-29T20:04:39

Today is another one of those days.

I'm doing a query against a table using a primary key and a check if two of the other columns are not the same.

SELECT column_list FROM table_name WHERE pk_column = some_var AND column_x != column_y;

One of the columns can be null. Apparently I have never tried to do this with Oracle until now.

Initially I expected that NULL is not equal to some non-NULL value in the same way that an undef is not equal some defined value. However, I know why Oracle does this (it's in the ANSI standard but no one else that I know of does it this way).

Don't Shoot Me...

Mr. Muskrat on 2007-08-29T20:22:18

... I didn't design it.

The new query looks like:
SELECT column_list FROM table_name WHERE pk_column = some_var AND ( column_y IS NULL OR column_x != column_y);

PostgreSQL too

grantm on 2007-08-29T21:50:44

Surely that behaviour is the same in PostgreSQL too? Any attempt to compare a NULL value to any other value (including another NULL) will result in NULL rather than true or false.

Re:PostgreSQL too

Mr. Muskrat on 2007-08-29T22:10:49

One of my coworkers was wondering what PostgreSQL did. I'll pass it on.

Logically incomparable?

Alias on 2007-08-29T23:20:45

I seem to recall hitting this once, and from what I could tell it reflects a sort of purist view by Oracle.

You have something, and you have nothing. And you can't compare something with nothing. Thus, they can't be "different" because there's nothing there to be different with.

Takes a bit of mind reversal...

NVL()

runrig on 2007-08-30T00:18:00

If you know that the possibly null column can not be some value (say a single space) or you want to assume null is the same as some value, then you can use:

where col1 != NVL(col2, ' ')

Re:NVL()

runrig on 2007-08-30T00:19:57

Oh, and it's not just Oracle (and PostgreSQL) that treats NULL's this way.

Re:NVL()

Mr. Muskrat on 2007-08-30T01:00:25

I haven't used a ton of databases and it's not a test that comes up all the time so I wasn't sure.

Re:NVL()

VSarkiss on 2007-08-30T22:32:35

And if you want to be SQL-92 compliant, you can use COALESCE, which works the same as NVL (but sounds better, even though it takes longer to type).

Re:NVL()

grinder on 2007-10-26T13:47:02

What does COALESCE do? (As opposed to NVL).

Re:NVL()

Aristotle on 2007-09-02T12:26:51

Is that right?

I am thinking right now that the correct way to write this test would be WHERE NVL(col1 != col2, FALSE). So it would return the resulting boolean value from all comparisons of existent values, but for comparisons involving NULL it would default the resulting NULL to FALSE. Basically you’re saying “compare the columns for inequality, and if they can’t be compared, then treat them as unequal.”

By doing it this way you avoid semi-predicate problems.

Re:NVL()

runrig on 2007-09-04T00:42:16

I see what you're saying...except that the NVL() should return TRUE if the '!=' result is null (at least that's what the OP seems to want). And I know he said that only one of the columns could be null, but I'm so used to potentially both columns being null, and wanting to treat that case as the columns being equal, that I'm used to wrapping the columns in NVL() individually...and not thinking about brilliant shortcuts :-)

MySQL too

perrin on 2007-08-30T17:10:23

MySQL does the same. I bet SQLite does too.

Re:MySQL too

Mr. Muskrat on 2007-08-30T17:23:25

Is that a 5.0 thing or has it always done it this way? I may be misremembering but I thought that MySQL 4 treated NULLs as either empty string or 0 depending on the equality tests.

Re:MySQL too

perrin on 2007-08-30T17:44:05

I'm pretty sure it has always been this way. It certainly is from 4.1 on.

Maybe I'm Just Braindead...

Mr. Muskrat on 2007-08-30T17:31:59

... but I really thought that I had seen a database treat NULLs like Perl treats undefs. I guess it's always possible that my memory is playing tricks on me. It certainly wouldn't be the first time. I did a lot of stupid stuff in my youth that almost certainly destroyed vast amounts of brain cells.

The absence of a value shouldn't prevent you from checking [in]equality. But maybe I'm the only one that thinks that way. :)